﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Threading;
using System.Globalization;
using System.Windows.Forms;
using System.Reflection;
using System.Collections;
using System.IO;
using System.Data.OleDb;
using System.Drawing.Imaging;
using System.Drawing;

namespace Public {
    public class ExportReport {
        #region 对象声明
        private static ExportReport _INSTANCE = null;
        protected object missing = Missing.Value;
       // private Microsoft.Office.Interop.Excel.Application rptExcel = null;

        private DataTable dt = new DataTable();
        private DataTable dtTemp = new DataTable();

        #endregion


        #region 其他导出
        private ExportReport() {
        }

        public static ExportReport INSTANCE() {
            if (null == _INSTANCE) {
                _INSTANCE = new ExportReport();
            }
            return _INSTANCE;
        }

        public DataTable ToDataTable(IList list) {
            DataTable result = new DataTable();
            if (list != null && list.Count > 0) {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys) {
                    result.Columns.Add(pi.Name, pi.PropertyType);
                }
                for (int i = 0; i < list.Count; i++) {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys) {
                        object obj = pi.GetValue(list[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }

            return result;
        }

        public void ExportExcel(DataTable dt, string saveFile, string title) {
            rptExcel = new Microsoft.Office.Interop.Excel.Application();
            if (null == rptExcel) {
                MessageBox.Show("无法打开EXcel，请检查Excel是否可用或者是否安装好Excel", "系统提示");
                return;
            }
            if (dt == null || dt.Rows.Count <= 0) {
                MessageBox.Show("没有数据", "系统提示");
                return;
            }
            int rowCount = dt.Rows.Count;//行数
            int columnCount = dt.Columns.Count;//列数
            float percent = 0;//导出进度
            //this.Cursor = Cursors.WaitCursor;
            //保存文化环境
            CultureInfo currentCI = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            Microsoft.Office.Interop.Excel.Workbook _workBook = rptExcel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet _workSheet = (Microsoft.Office.Interop.Excel.Worksheet)_workBook.Sheets.get_Item(1);
            _workSheet.Name = "报表";//一个sheet的名称
            //rptExcel.Visible = true;//打开导出的Excel文件
            _workSheet.Cells[1, 1] = title;//表标题
            //填充列标题
            for (int i = 0; i < columnCount; i++) {
                _workSheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
            }

            //创建对象数组存储DataTable的数据，这样的效率比直接将Datateble的数据填充worksheet.Cells[row,col]高
            object[,] objData = new object[rowCount, columnCount];
            FrmProgressBar panelProgress = new FrmProgressBar();
            panelProgress.Show();
            //填充内容到对象数组
            for (int r = 0; r < rowCount; r++) {
                for (int col = 0; col < columnCount; col++) {
                    //objData[r, col] = dt.Rows[r][col].ToString();
                    _workSheet.Cells[3 + r, col + 1] = dt.Rows[r][col].ToString();
                }
                percent = ((float)(r + 1) * 100) / rowCount;
                panelProgress._进度条.Text = percent.ToString("n") + "%";
                panelProgress.progressBar1.Value = Convert.ToInt32(percent);
                System.Windows.Forms.Application.DoEvents();
            }

            //将对象数组的值赋给Excel对象
            //Microsoft.Office.Interop.Excel.Range range = _workSheet.get_Range(_workSheet.Cells[3, 1], _workSheet.Cells[rowCount + 2, columnCount]);
            //range.NumberFormat = "#,##0.00";//设置数字文本格式
            //range.Value2 = objData;
            //设置格式
            _workSheet.get_Range(_workSheet.Cells[1, 1], _workSheet.Cells[1, columnCount]).MergeCells = true;//合并单元格
            _workSheet.get_Range(_workSheet.Cells[1, 1], _workSheet.Cells[1, columnCount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
            _workSheet.get_Range(_workSheet.Cells[1, 1], _workSheet.Cells[1, columnCount]).RowHeight = 38;
            _workSheet.get_Range(_workSheet.Cells[1, 1], _workSheet.Cells[1, columnCount]).Font.Bold = true;
            _workSheet.get_Range(_workSheet.Cells[1, 1], _workSheet.Cells[1, columnCount]).Font.Name = "黑体";
            _workSheet.get_Range(_workSheet.Cells[1, 1], _workSheet.Cells[1, columnCount]).Font.Size = 16;

            _workSheet.get_Range(_workSheet.Cells[2, 1], _workSheet.Cells[rowCount + 2, columnCount]).Borders.LineStyle = 1;//设置边框
            _workSheet.get_Range(_workSheet.Cells[2, 1], _workSheet.Cells[rowCount, columnCount]).Columns.AutoFit();//设置单元格宽度为自适应

            //恢复文化环境
            Thread.CurrentThread.CurrentCulture = currentCI;
            try {
                //rptExcel.Save(saveFile); //自动创建一个新的Excel文档保存在“我的文档”里，如果不用SaveFileDialog就可用这种方法
                _workBook.Saved = true;
                _workBook.SaveCopyAs(saveFile);//以复制的形式保存在已有的文档里
                panelProgress.Close();//隐藏进度条
                MessageBox.Show("数据已经成功导出为Excel文件！", "成功导出");
            } catch (Exception ex) {
                MessageBox.Show("导出文件出错，文件可能正被打开，具体原因："
                    + ex.Message, "出错信息");

            } finally {
                dt.Dispose();
                rptExcel.Quit();
                KillSpecialExcel();
                Marshal.ReleaseComObject(rptExcel);
                Marshal.ReleaseComObject(_workSheet);
                Marshal.ReleaseComObject(_workBook);
                GC.Collect();
            }
        }

        public void ExportExcel(DataGridView dt, string saveFile, string _Title) {
            rptExcel = new Microsoft.Office.Interop.Excel.Application();

            if (null == rptExcel) {
                MessageBox.Show("无法打开EXcel，请检查Excel是否可用或者是否安装好Excel", "系统提示");
                return;
            }
            if (dt == null || dt.Rows.Count <= 0) {
                MessageBox.Show("没有数据", "系统提示");
                return;
            }
            int _RowCount = dt.Rows.Count;//行数
            int _ColumnCount = dt.Columns.Count;//列数

            float _Percent = 0;//导出进度
            //this.Cursor = Cursors.WaitCursor;
            //保存文化环境
            CultureInfo currentCI = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            Microsoft.Office.Interop.Excel.Workbook _WorkBook = rptExcel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet _WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)_WorkBook.Sheets.get_Item(1);
            _WorkSheet.Name = "报表";//一个sheet的名称
            //rptExcel.Visible = true;//打开导出的Excel文件
            _WorkSheet.Cells[1, 1] = _Title;//表标题
            //填充列标题

            int _ColumnCountVisibled = 0;//可见列
            for (int i = 0; i < _ColumnCount; i++) {
                if (dt.Columns[i].Visible) {
                    //Console.WriteLine(dt.Columns[i].Name);
                    _WorkSheet.Cells[2, 1 + _ColumnCountVisibled++] = dt.Columns[i].HeaderText;
                }
            }

            //创建对象数组存储DataTable的数据，这样的效率比直接将Datateble的数据填充worksheet.Cells[row,col]高
            string[,] objData = new string[_RowCount, _ColumnCountVisibled];
            FrmProgressBar panelProgress = new FrmProgressBar();
            panelProgress.Show();
            //填充内容到对象数组
            for (int r = 0; r < _RowCount; r++) {
                int t = 0;
               // DataGridViewRow row = dt.Rows[r];
                for (int col = 0; col < _ColumnCount; col++) {
                    if (dt.Columns[col].Visible) {
                        //objData[r, t] = row.Cells[col].Value;
                        if (dt[col, r].Value+""!="" && dt[col, r].Value.GetType().ToString() == "System.DateTime")
                        {
                            objData[r, t] = Convert.ToDateTime(dt[col, r].Value).ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        else
                        {
                            objData[r, t] = dt[col, r].Value + "";
                        }
                        t++;
                    }
                }
                _Percent = ((float)(r + 1) * 100) / _RowCount;
                panelProgress._进度条.Text = _Percent.ToString("n") + "%";
                panelProgress.progressBar1.Value = Convert.ToInt32(_Percent);
                System.Windows.Forms.Application.DoEvents();
            }
            //将对象数组的值赋给Excel对象
            Microsoft.Office.Interop.Excel.Range range = _WorkSheet.Range[_WorkSheet.Cells[3, 1], _WorkSheet.Cells[_RowCount + 2, _ColumnCountVisibled]];
            //range.NumberFormat = "#,##0.00";//设置数字文本格式
            //range.NumberFormat = "###";
            range.Value2 = objData;
            //设置格式
            _WorkSheet.Range[_WorkSheet.Cells[1, 1], _WorkSheet.Cells[1, _ColumnCountVisibled]].MergeCells = true;//合并单元格
            _WorkSheet.Range[_WorkSheet.Cells[1, 1], _WorkSheet.Cells[1, _ColumnCountVisibled]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
            _WorkSheet.Range[_WorkSheet.Cells[1, 1], _WorkSheet.Cells[1, _ColumnCountVisibled]].RowHeight = 38;
            _WorkSheet.Range[_WorkSheet.Cells[1, 1], _WorkSheet.Cells[1, _ColumnCountVisibled]].Font.Bold = true;
            _WorkSheet.Range[_WorkSheet.Cells[1, 1], _WorkSheet.Cells[1, _ColumnCountVisibled]].Font.Name = "黑体";
            _WorkSheet.Range[_WorkSheet.Cells[1, 1], _WorkSheet.Cells[1, _ColumnCountVisibled]].Font.Size = 16;
            _WorkSheet.Range[_WorkSheet.Cells[2, 1], _WorkSheet.Cells[_RowCount + 2, _ColumnCountVisibled]].Borders.LineStyle = 1;//设置边框
            _WorkSheet.Range[_WorkSheet.Cells[2, 1], _WorkSheet.Cells[_RowCount, _ColumnCountVisibled]].Columns.AutoFit();//设置单元格宽度为自适应

            //恢复文化环境
            Thread.CurrentThread.CurrentCulture = currentCI;
            try {
                //rptExcel.Save(saveFile); //自动创建一个新的Excel文档保存在“我的文档”里，如果不用SaveFileDialog就可用这种方法
                _WorkBook.Saved = true;
                _WorkBook.SaveCopyAs(saveFile);//以复制的形式保存在已有的文档里
                //this.Cursor = Cursors.Default;
                panelProgress.Close();//隐藏进度条
                MessageBox.Show("数据已经成功导出为Excel文件！", "成功导出", MessageBoxButtons.OK, MessageBoxIcon.Information);
            } catch (Exception ex) {
                MessageBox.Show("导出文件出错，文件可能正被打开，具体原因：" + ex.Message, "出错信息", MessageBoxButtons.OK, MessageBoxIcon.Error);

            } finally {
                // dt.Dispose();
                rptExcel.Quit();
                KillSpecialExcel();
                Marshal.ReleaseComObject(rptExcel);
                Marshal.ReleaseComObject(_WorkSheet);
                Marshal.ReleaseComObject(_WorkBook);
                GC.Collect();
                //KillAllExcel();

            }
        }

        public void ExportExcel(DataGridView dt, string saveFile)
        {
            rptExcel = new Microsoft.Office.Interop.Excel.Application();

            if (null == rptExcel)
            {
                MessageBox.Show("无法打开EXcel，请检查Excel是否可用或者是否安装好Excel", "系统提示");
                return;
            }
            if (dt == null || dt.Rows.Count <= 0)
            {
                MessageBox.Show("没有数据", "系统提示");
                return;
            }
            int _RowCount = dt.Rows.Count;//行数
            int _ColumnCount = dt.Columns.Count;//列数

            float _Percent = 0;//导出进度
            //this.Cursor = Cursors.WaitCursor;
            //保存文化环境
            CultureInfo currentCI = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            Microsoft.Office.Interop.Excel.Workbook _WorkBook = rptExcel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet _WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)_WorkBook.Sheets.get_Item(1);
            _WorkSheet.Name = "Sheet1";//一个sheet的名称
            //rptExcel.Visible = true;//打开导出的Excel文件
            //_WorkSheet.Cells[1, 1] = _Title;//表标题
            //填充列标题

            int _ColumnCountVisibled = 0;//可见列
            for (int i = 0; i < _ColumnCount; i++)
            {
                if (dt.Columns[i].Visible)
                {
                    //Console.WriteLine(dt.Columns[i].Name);
                    _WorkSheet.Cells[1, 1 + _ColumnCountVisibled++] = dt.Columns[i].HeaderText;
                }
            }

            //创建对象数组存储DataTable的数据，这样的效率比直接将Datateble的数据填充worksheet.Cells[row,col]高
            object[,] objData = new object[_RowCount, _ColumnCountVisibled];
            FrmProgressBar panelProgress = new FrmProgressBar();
            panelProgress.Show();
            //填充内容到对象数组
            for (int r = 0; r < _RowCount; r++)
            {
                int t = 0;
                DataGridViewRow row = dt.Rows[r];
                for (int col = 0; col < _ColumnCount; col++)
                {
                    if (dt.Columns[col].Visible)
                    {
                        objData[r, t] = row.Cells[col].Value;
                        t++;
                    }
                }
                _Percent = ((float)(r + 1) * 100) / _RowCount;
                panelProgress._进度条.Text = _Percent.ToString("n") + "%";
                panelProgress.progressBar1.Value = Convert.ToInt32(_Percent);
                System.Windows.Forms.Application.DoEvents();
            }
            //将对象数组的值赋给Excel对象
            Microsoft.Office.Interop.Excel.Range range = _WorkSheet.get_Range(_WorkSheet.Cells[2, 1], _WorkSheet.Cells[_RowCount + 1, _ColumnCountVisibled]);
            range.NumberFormat = "@";//设置文本格式
            //range.NumberFormat = "###";
            range.Value2 = objData;
            //设置格式
            //_WorkSheet.get_Range(_WorkSheet.Cells[1, 1], _WorkSheet.Cells[1, _ColumnCountVisibled]).MergeCells = true;//合并单元格
            //_WorkSheet.get_Range(_WorkSheet.Cells[1, 1], _WorkSheet.Cells[1, _ColumnCountVisibled]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
            //_WorkSheet.get_Range(_WorkSheet.Cells[1, 1], _WorkSheet.Cells[1, _ColumnCountVisibled]).RowHeight = 38;
            //_WorkSheet.get_Range(_WorkSheet.Cells[1, 1], _WorkSheet.Cells[1, _ColumnCountVisibled]).Font.Bold = true;
            //_WorkSheet.get_Range(_WorkSheet.Cells[1, 1], _WorkSheet.Cells[1, _ColumnCountVisibled]).Font.Name = "黑体";
            //_WorkSheet.get_Range(_WorkSheet.Cells[1, 1], _WorkSheet.Cells[1, _ColumnCountVisibled]).Font.Size = 16;
            //_WorkSheet.get_Range(_WorkSheet.Cells[2, 1], _WorkSheet.Cells[_RowCount + 2, _ColumnCountVisibled]).Borders.LineStyle = 1;//设置边框
            _WorkSheet.get_Range(_WorkSheet.Cells[1, 1], _WorkSheet.Cells[_RowCount + 1, _ColumnCountVisibled]).NumberFormat = "@";
            _WorkSheet.get_Range(_WorkSheet.Cells[1, 1], _WorkSheet.Cells[_RowCount, _ColumnCountVisibled]).Columns.AutoFit();//设置单元格宽度为自适应

            //恢复文化环境
            Thread.CurrentThread.CurrentCulture = currentCI;
            try
            {
                //rptExcel.Save(saveFile); //自动创建一个新的Excel文档保存在“我的文档”里，如果不用SaveFileDialog就可用这种方法
                _WorkBook.Saved = true;
                _WorkBook.SaveCopyAs(saveFile);//以复制的形式保存在已有的文档里
                //this.Cursor = Cursors.Default;
                panelProgress.Close();//隐藏进度条
                //MessageBox.Show("数据已经成功导出为Excel文件！", "成功导出", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出文件出错，文件可能正被打开，具体原因：" + ex.Message, "出错信息", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            finally
            {
                // dt.Dispose();
                rptExcel.Quit();
                KillSpecialExcel();
                Marshal.ReleaseComObject(rptExcel);
                Marshal.ReleaseComObject(_WorkSheet);
                Marshal.ReleaseComObject(_WorkBook);
                GC.Collect();
                //KillAllExcel();

            }
        }


        public void ExportExcel(DataTable dt, string _输出文件路径, string _title, string _模板路径, string[] cols) {
            if (_模板路径 == null)
                throw new Exception("Excel模板文件路径不能为空！");

            if (_输出文件路径 == null)
                throw new Exception("输出Excel文件路径不能为空！");

            if (!File.Exists(_模板路径))
                throw new Exception("指定路径的Excel模板文件不存在！");

            //创建一个Application对象
            rptExcel = new Microsoft.Office.Interop.Excel.Application();
            rptExcel.Visible = false;

            //打开模板文件
            Microsoft.Office.Interop.Excel.Workbook workBook =
                rptExcel.Workbooks._Open(_模板路径, missing, missing, missing, missing, missing,
                missing, missing, missing, missing, missing, missing, missing);
            Microsoft.Office.Interop.Excel.Worksheet sheet =
                (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.get_Item(1);

            FrmProgressBar panelProgress = new FrmProgressBar();
            panelProgress.Show();
            float percent;

            int _FirstRowIndex = 2;//起始行
            int _Rows = 1;
            int rowCount = dt.Rows.Count;
            object[,] objData = new object[rowCount, cols.Length];
            for (int r = 0; r < rowCount; r++) {
                for (int i = 0; i < cols.Length; i++) {
                    objData[r, i] = dt.Rows[r][cols[i]];
                }
                percent = ((float)(_Rows++) * 100) / rowCount;
                panelProgress._进度条.Text = percent.ToString("n") + "%";
                panelProgress.progressBar1.Value = Convert.ToInt32(percent);
                System.Windows.Forms.Application.DoEvents();
            }

            //将对象数组的值赋给Excel对象
            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(sheet.Cells[_FirstRowIndex + 1, 1],
                                                                                                                         sheet.Cells[_FirstRowIndex + rowCount, cols.Length]);
            //range.NumberFormat = "#,##0.00";//设置数字文本格式
            range.Borders.LineStyle = 1;
            range.Value2 = objData;
            try {
                workBook.Saved = true;
                workBook.SaveCopyAs(_输出文件路径);//以复制的形式保存在已有的文档里
                panelProgress.Close();//隐藏进度条
                MessageBox.Show("数据已经成功导出为Excel文件！", "成功导出");
            } catch (Exception ex) {
                MessageBox.Show("导出文件出错，文件可能正被打开，具体原因："
                    + ex.Message, "出错信息");
            } finally {
                rptExcel.Quit();
                KillSpecialExcel();
                Marshal.ReleaseComObject(rptExcel);
                Marshal.ReleaseComObject(sheet);
                Marshal.ReleaseComObject(workBook);
                GC.Collect();
                //KillAllExcel();
            }
        }

    


        [DllImport("user32.dll", SetLastError = true)]
        static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);
        public void KillSpecialExcel() {
            try {
                if (rptExcel != null) {
                    int lpdwProcessId;
                    GetWindowThreadProcessId(new IntPtr(rptExcel.Hwnd), out lpdwProcessId);
                    System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                }
            } catch (Exception ex) {
                Console.WriteLine("Delete Excel Process Error:" + ex.Message);
            }
        }

        private void KillAllExcel() {
            Process[] processes = Process.GetProcesses();
            for (int i = 0; i < processes.Length; i++) {
                if (processes[i].ProcessName.ToUpper() == "EXCEL") {
                    processes[i].Kill();
                }
            }
        }
        #endregion


        #region 导出TXT
        public void 导出文本(DataTable dt, string _输出文件路径, string[] cols) {
            if (_输出文件路径 == null)
                throw new Exception("输出文本文件路径不能为空！");
            UTF8Encoding utf8 = new UTF8Encoding();
            FileStream aFile = new FileStream(_输出文件路径, FileMode.Create);
            StreamWriter sw = new StreamWriter(aFile, Encoding.UTF8);//utf8
            try {
                foreach (DataRow row in dt.Rows) {
                    StringBuilder sb = new StringBuilder();
                    foreach (string col in cols) {
                        sb.Append(row[col] + "" + (char)9);//TAB分隔                        
                    }
                    string s = sb.ToString().TrimEnd();//去掉尾部TAB
                    Byte[] encodedBytes = utf8.GetBytes(s);
                    String decodedString = utf8.GetString(encodedBytes);

                    sw.Write(decodedString);
                    sw.WriteLine();
                }

            } catch (Exception ex) {
                throw ex;
            } finally {
                sw.Flush();
                sw.Close();
                sw.Dispose();
                aFile.Close();
            }
        }

        public void 导出文本IMEI_NETCODE(DataTable dt, string _输出文件路径, string[] cols) {
            if (_输出文件路径 == null)
                throw new Exception("输出文本文件路径不能为空！");
            UTF8Encoding utf8 = new UTF8Encoding();
            FileStream aFile = new FileStream(_输出文件路径, FileMode.Create);
            StreamWriter sw = new StreamWriter(aFile, Encoding.UTF8);//utf8
            try {
                foreach (DataRow row in dt.Rows) {
                    StringBuilder sb = new StringBuilder();
                    foreach (string col in cols) {
                        sb.Append(row[col] + ",");//TAB分隔                        
                    }
                    string s = sb.ToString().TrimEnd(new char[] { ',' });//去掉尾部TAB
                    Byte[] encodedBytes = utf8.GetBytes(s);
                    String decodedString = utf8.GetString(encodedBytes);

                    sw.Write(decodedString);
                    sw.WriteLine();
                }

            } catch (Exception ex) {
                //WriteLogTools.WriteMessage(ex.Message);
            } finally {
                sw.Flush();
                sw.Close();
                sw.Dispose();
                aFile.Close();
            }
        }


        public void 导出文本_ENDTRIM(DataTable dt, string _输出文件路径, string[] cols) {
            if (_输出文件路径 == null)
                throw new Exception("输出文本文件路径不能为空！");
            UTF8Encoding utf8 = new UTF8Encoding();
            FileStream aFile = new FileStream(_输出文件路径, FileMode.Create);
            StreamWriter sw = new StreamWriter(aFile, Encoding.UTF8);//utf8
            try {
                foreach (DataRow row in dt.Rows) {
                    StringBuilder sb = new StringBuilder();
                    foreach (string col in cols) {
                        sb.Append((row[col] == null ? "" : row[col]) + "" + (char)9);//TAB分隔                        
                    }
                    //for (int i = 0; i < cols.Length; i++)
                    //{
                    //    sb.Append((row[i]==null?"":row[i]) + "" + (char)9);//TAB分隔 
                    //}
                    string s = sb.ToString().TrimEnd();//去掉尾部TAB
                    Byte[] encodedBytes = utf8.GetBytes(s);
                    String decodedString = utf8.GetString(encodedBytes);

                    sw.Write(decodedString);
                    sw.WriteLine();
                }

            } catch (Exception ex) {
                throw ex;
            } finally {
                sw.Flush();
                sw.Close();
                sw.Dispose();
                aFile.Close();
            }
        }
        #endregion

        #region 导入EXCEL
        public DataTable ImportExcel(string _FilePath) {
            string sConnectString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + _FilePath + ";Extended Properties=Excel 8.0";
            if (_FilePath.ToUpper().EndsWith(".XLSX")) {
                sConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _FilePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
            }
            OleDbConnection connection = new OleDbConnection(sConnectString);
            try {
                OleDbDataAdapter adp = new OleDbDataAdapter("select * from [Sheet1$]", connection);
                DataSet ds = new DataSet();
                adp.Fill(ds, "Book1");
              
                DataTable dt = ds.Tables[0];
                foreach (DataColumn dcol in dt.Columns) {
                    dcol.ColumnName = dcol.ColumnName.Trim();
                }
                return ds.Tables[0];
            } finally {
                connection.Close();
            }
        }

        public DataTable ImportExcel(string FilePath, string a) {
            //object Nothing = System.Reflection.Missing.Value;

            //创建一个Application对象
            rptExcel = new Microsoft.Office.Interop.Excel.Application();
            rptExcel.Visible =true;
            //app.UserControl = true;
            //app.DisplayAlerts = false;

            Microsoft.Office.Interop.Excel.Workbook workBook1 =
                rptExcel.Workbooks._Open(FilePath, missing, missing, missing, missing, missing,
                missing, missing, missing, missing, missing, missing, missing);
            Microsoft.Office.Interop.Excel.Worksheet sheet1 =
                (Microsoft.Office.Interop.Excel.Worksheet)workBook1.Worksheets.get_Item(1);

            //app.Application.Workbooks.Open(FilePath, Nothing, Nothing, Nothing, Nothing,
            //Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
            //Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks;
            //Microsoft.Office.Interop.Excel.Workbook workbook1 = wbs[1];
            //Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook1.Worksheets.get_Item(1);//"Sheet2"

            Microsoft.Office.Interop.Excel.Range range1 = null;
            DataTable dt1 = new DataTable();
            FrmProgressBar panelProgress = new FrmProgressBar();
            try {

                int rowCount = sheet1.UsedRange.Rows.Count;
                int colCount = sheet1.UsedRange.Columns.Count;
                if (rowCount <= 0)
                    throw new Exception("文件中没有数据记录");
                if (colCount <= 0)
                    throw new Exception("字段个数不对");

                panelProgress.Show();
                float percent;

                for (int i = 0; i < rowCount; i++) {//循环行
                    object[] rows = new object[colCount];
                    for (int j = 0; j < colCount; j++) {//循环列
                        range1 = sheet1.Range[sheet1.Cells[i + 1, j + 1], sheet1.Cells[i + 1, j + 1]];
                        //object va=range1.MergeCells;
                        //Console.WriteLine("[" + i + "," + j + "]=" + range1.Value2 +"   "+ va.ToString());
                        
                        rows[j] = range1.Value;
                    }
                    if (i == 0) {//单头列
                        for (int j = 0; j < rows.Length; j++) {
                            if (rows[j] == null) {
                                rows[j] = "f" + j;
                            }
                            DataColumn dc = new DataColumn();
                            dc.ColumnName = rows[j].ToString();
                            dt1.Columns.Add(dc);
                        }
                    } else {
                        DataRow dr = dt1.NewRow();
                        for (int j = 0; j < rows.Length; j++) {
                            if (rows[j] == null)
                                rows[j] = "";
                            dr[j] = rows[j];
                        }
                        dt1.Rows.Add(dr);
                    }
                    percent = ((float)(i) * 100) / rowCount;
                    panelProgress._进度条.Text = percent.ToString("n") + "%";
                    panelProgress.progressBar1.Value = Convert.ToInt32(percent);
                    System.Windows.Forms.Application.DoEvents();
                }

            } finally {
                panelProgress.Close();//隐藏进度条
                rptExcel.Quit();
                KillSpecialExcel();
                Marshal.ReleaseComObject(rptExcel);
                Marshal.ReleaseComObject(sheet1);
                Marshal.ReleaseComObject(workBook1);
                GC.Collect();
            }

            return dt1;
        }

        public DataTable ImportExcelSum(string _FilePath, string sql) {
            string sConnectString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + _FilePath + ";Extended Properties=Excel 8.0";
            if (_FilePath.ToUpper().EndsWith(".XLSX")) {
                sConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _FilePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
            }
            OleDbConnection connection = new OleDbConnection(sConnectString);
            try {
                //select * from [Sheet1$]
                OleDbDataAdapter adp = new OleDbDataAdapter(sql, connection);
                DataSet ds = new DataSet();
                adp.Fill(ds, "Book1");
                DataTable dt = ds.Tables[0];
                foreach (DataColumn dcol in dt.Columns) {
                    dcol.ColumnName = dcol.ColumnName.Trim();
                }
                return ds.Tables[0];
            } finally {
                connection.Close();
            }
        }
        #endregion
    }
}
